项目实战01：基于keepalived双vip的MySQL高可用集群 |
您所在的位置：网站首页 › keepalived mysql57主从 › 项目实战01：基于keepalived双vip的MySQL高可用集群 |
项目实战01：基于keepalived双vip的MySQL高可用集群
项目实战01：基于keepalived双vip的MySQL高可用集群
文章目录
项目实战01：基于keepalived双vip的MySQL高可用集群一、机器准备及IP地址规划二、使用ansible部署MySQL集群1、建立免密通道2、安装ansible3、使用ansible部署MySQL集群
三、MySQL集群的配置异步复制1、首先使用msyqldump将master中的所有数据导出，然后在slave上面备份，防止主库和从库数据不一致2、使用scp将备份文件all_sql.db传到slave1和slave2以及backup机器上。(也可以使用ansible)3、slave和backup导入数据4、master和slave开启二进制日志和server_id5、在master上创建一个有备份权限的用户，然后在slave上添加授权用户信息6、查看master状态信息7、在slave和backup上添加此授权用户信息8、在slave上面开启I/O线程和sql线程9、测试
四、基于GTID的主从复制1、关闭slave上的服务2、配置master和slave的配置文件3、重启master和slave的mysql服务
五、backup配置延迟备份1、关闭backup上的slave服务2、在backup_server上设置延迟时间，然后开启slave,这里设置60分钟3、测试效果
六、mysqlrouter读写分离部署实现1、准备两台全新的linux服务器，安装好mysqlrouter软件2、修改mysqlrouter的配置文件3、创建授权用户验证读写分离4、启动MySQLrouter
七、搭建双vip高可用集群1、搭建过程2、启动keepalived3、双VIP效果
八、搭建监控系统1、安装Prometheus2、访问测试3、节点安装和启动node-exporter4、测试访问5、在prometheus server里添加node节点6、测试访问7、安装grafana8、监控展示
十、验证结果及压力测试十一、总结
一、机器准备及IP地址规划
ansible 2.9.27
ip:192.168.1.144
prometheus 监控
192.168.1.103
MySQL Router 8.0.32
mysql集群
master 192.168.1.150
slave 192.168.1.151
slave 192.168.1.152
延迟备份 slave 192.168.1.153
keepalived及mysqlrouter
192.168.1.148
192.168.1.149
项目名称：基于MySQLrouter、keepalived双vip的MySQL高可用集群
项目环境：8台服务器（1G，1核），centos7.9 mysq5.7.35 mysqlrouter8.0.28 keepalived1.3.5 zabbix ansible
项目描述：
本项目的目的是构建一个高可用的能实现读写分离的高效的MySQL集群，确保业务的稳定，能沟通方便的监控整个集群，同时能批量的去部署和管理整个集群。
项目步骤：
1.安装好centos7.8的系统，部署好ansible，在所有的机器之间配置SSH免密通道
2.部署好zabbix监控系统
3.通过ansible去以二进制方式安装部署MySQL，主要是通过编写好的脚本一键安装二进制版本的MySQL。
4.使用ansible安装mysqlrouter 和keepalived，在另外2台中间件服务器上，实现读写分离和高可用，在keepalived上配置2个实例，实现2个vip，互为master和backup，更加好的提升高可用的性能。
5.在3台MySQL服务器上配置好主从复制，建立读写分离使用的用户，形成一个master+2个slave节点(半同步+GTID)的集群，提供数据库服务，部署一台延迟备份的服务器（延迟30分钟）
6.尝试部署mysql的MHA，实现自动的故障切换，确保master宕机，能自动提升另外一台slave为主
7.验证测试读写分离和高可用以及主从的failover
8.使用压力测试软件（sysbench）测试整个MySQL集群的性能（cpu、IO、内存等）
项目心得：
1.一定要规划好整个集群的架构，配置要细心，脚本要提前准备好，边做边修改
2.防火墙和selinux的问题需要多注意
3.对MySQL的集群、读写分离、故障切换和高可用有了深入的理解
4.对自动化批量部署和监控有了更加多的应用和理解
5.keepalived的配置需要更加细心和IP地址的规划有了新的认识
二、使用ansible部署MySQL集群
1、建立免密通道
[root@ansible ansible]# ssh-keygen #生成密钥，默认回车即可
[root@ansible ansible]# ssh-copy-id [email protected] #拷贝密钥至主机
[root@ansible ansible]# ssh-copy-id [email protected] #拷贝密钥至主机
[root@ansible ansible]# ssh-copy-id [email protected] #拷贝密钥至主机
[root@ansible ansible]# ssh-copy-id [email protected] #拷贝密钥至主机
2、安装ansible
[root@ansible ansible]# yum install epel-release -y
[root@ansible ansible]# yum install ansible -y
[root@ansible ansible]# cat hosts
[mysql]
192.168.1.150
192.168.1.151
192.168.1.152
192.168.1.153
[root@ansible ansible]# ansible mysql -m ping
192.168.1.150 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
192.168.1.152 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
192.168.1.151 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
192.168.1.153 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
[root@ansible ansible]#
# 以上结果就说明，ansible已经全部安装成功并且连接成功。
3、使用ansible部署MySQL集群
# 编写一键安装MySQL脚本，如下：
[root@ansible ansible]# cat onekey_install_mysql_binary.sh
#!/bin/bash
#解决软件的依赖关系
yum install cmake ncurses-devel gcc gcc-c++ vim lsof bzip2 openssl-devel ncurses-compat-libs -y
#mkdir /mysql
cd /mysql
#解压mysql二进制安装包
tar xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
#移动mysql解压后的文件到/usr/local下改名叫mysql
mv mysql-5.7.35-linux-glibc2.12-x86_64 /usr/local/mysql
#新建组和用户 mysql
groupadd mysql
#mysql这个用户的shell 是/bin/false 属于mysql组
useradd -r -g mysql -s /bin/false mysql
#关闭firewalld防火墙服务，并且设置开机不要启动
service firewalld stop
systemctl disable firewalld
#临时关闭selinux
setenforce 0
#永久关闭selinux
sed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config
#新建存放数据的目录
mkdir /data/mysql -p
#修改/data/mysql目录的权限归mysql用户和mysql组所有，这样mysql用户可以对这个文件夹进行读写了
chown mysql:mysql /data/mysql/
#只是允许mysql这个用户和mysql组可以访问，其他人都不能访问
chmod 750 /data/mysql/
#进入/usr/local/mysql/bin目录
cd /usr/local/mysql/bin/
#初始化mysql
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql &>passwd.txt
#让mysql支持ssl方式登录的设置
./mysql_ssl_rsa_setup --datadir=/data/mysql/
#获得临时密码
tem_passwd=$(cat passwd.txt |grep "temporary"|awk '{print $NF}')
#$NF表示最后一个字段
# abc=$(命令) 优先执行命令，然后将结果赋值给abc
# 修改PATH变量，加入mysql bin目录的路径
#临时修改PATH变量的值
export PATH=/usr/local/mysql/bin/:$PATH
#重新启动linux系统后也生效，永久修改
echo 'PATH=/usr/local/mysql/bin:$PATH' >>/root/.bashrc
#复制support-files里的mysql.server文件到/etc/init.d/目录下叫mysqld
cp ../support-files/mysql.server /etc/init.d/mysqld
#修改/etc/init.d/mysqld脚本文件里的datadir目录的值
sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld
#生成/etc/my.cnf配置文件
cat >/etc/my.cnf >/etc/rc.local
chmod +x /etc/rc.d/rc.local
#启动mysqld进程
service mysqld start
#将mysqld添加到linux系统里服务管理名单里
/sbin/chkconfig --add mysqld
#设置mysqld服务开机启动
/sbin/chkconfig mysqld on
#初次修改密码需要使用--connect-expired-password 选项
#-e 后面接的表示是在mysql里需要执行命令 execute 执行
#set password='Sanchuang123#'; 修改root用户的密码为Sanchuang123#
mysql -uroot -p$tem_passwd --connect-expired-password -e "set password='Sanchuang123#';"
#检验上一步修改密码是否成功，如果有输出能看到mysql里的数据库，说明成功。
mysql -uroot -p'Sanchuang123#' -e "show databases;"
# 使用ansible将mysql源码传过去。
[root@ansible ansible]# ansible mysql -m copy -a 'src=/ansible/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz dest=/mysql/'
# 使用ansible编写playbook执行安装。
[root@ansible ansible]# cat onekey_install_mysql.yaml
---
- name: 安装mysql
hosts: mysql
gather_facts: yes
tasks:
- name: 安装mysql
script: /ansible/onekey_install_mysql_binary.sh
[root@ansible ansible]# ansible-playbook onekey_install_mysql.yaml
# 至此MySQL集群已经搭建完成。
三、MySQL集群的配置异步复制
master上删除数据库 root@(none) 18:38 mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysqlslap | | performance_schema | | sc | | sys | | xw | +--------------------+ 7 rows in set (0.00 sec) root@(none) 18:42 mysql>drop database xw; Query OK, 0 rows affected (0.01 sec) root@(none) 18:42 mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysqlslap | | performance_schema | | sc | | sys | +--------------------+从库中的这个数据库也被删掉了 root@(none) 18:40 mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysqlslap | | performance_schema | | sc | | sys | +--------------------+ 6 rows in set (0.00 sec) 四、基于GTID的主从复制 1、关闭slave上的服务 stop slave 2、配置master和slave的配置文件 gtid-mode=on enforce-gtid-consistency = on 3、重启master和slave的mysql服务 service mysqld restart 五、backup配置延迟备份 1、关闭backup上的slave服务 stop slave 2、在backup_server上设置延迟时间，然后开启slave,这里设置60分钟 CHANGE MASTER TO MASTER_DELAY = 3000; start slave; show slave status\G;
master上删除数据库 root@(none) 19:17 mysql>drop database sc; Query OK, 2 rows affected (0.02 sec)60分钟后，backup上的才删掉，而slave的马上就删掉了。 #这里是backup的mysql root@(none) 20:18 mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysqlslap | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) 六、mysqlrouter读写分离部署实现 MySQL Router是MySQL官方提供的一个轻量级中间件，可以在应用程序与MySQL服务器之间提供透明的路由方式。主要用以解决MySQL主从库集群的高可用、负载均衡、易扩展等问题。 1、准备两台全新的linux服务器，安装好mysqlrouter软件 proxy-1 ：192.168.1.148 proxy-2： 192.168.1.149 2、修改mysqlrouter的配置文件proxy-1配置如下 [DEFAULT] user = mysql logging_folder = /data/mysql/mysql-router-8.0.28/log plugin_folder = /data/mysql/mysql-router-8.0.28/lib/mysqlrouter runtime_folder = /data/mysql/mysql-router-8.0.28/run data_folder = /data/mysql/mysql-router-8.0.28/data [logger] level = INFO filename = mysqlrouter.log # 名字可以自定义 [routing:read_write] bind_address = 0.0.0.0 #将mysqlroute绑定的IP地址改为0.0.0.0，本机任意ip地址都能访问 bind_port = 7001 #支持可读可写 mode = read-write #mysql-master服务器的ip地址：mysql服务的端口号 destinations = 192.168.1.150:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 9 [routing:read_only] bind_address = 0.0.0.0 bind_port = 7002 # 仅可读 mode = read-only # mysql-slave服务器的ip地址：mysql服务的端口号 destinations = 192.168.1.150:3306,192.168.1.151:3306,192.168.1.152:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 9proxy-2配置如下 [DEFAULT] user = mysql logging_folder = /data/mysql/mysql-router-8.0.28/log plugin_folder = /data/mysql/mysql-router-8.0.28/lib/mysqlrouter runtime_folder = /data/mysql/mysql-router-8.0.28/run data_folder = /data/mysql/mysql-router-8.0.28/data [logger] level = INFO filename = mysqlrouter.log # 名字可以自定义 [routing:read_write] bind_address = 0.0.0.0 bind_port = 7001 #支持可读可写 mode = read-write #mysql-master服务器的ip地址：mysql服务的端口号 destinations = 192.168.1.150:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 9 [routing:read_only] bind_address = 0.0.0.0 bind_port = 7002 # 仅可读 mode = read-only # mysql-slave服务器的ip地址：mysql服务的端口号 destinations = 192.168.1.150:3306,192.168.1.151:3306,192.168.1.152:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 9 3、创建授权用户验证读写分离read-write —>7001 —> 192.168.1.150 写操作在master执行 read-only —>7002 —> 192.168.44.150:3306,192.168.44.151:3306,192.168.44.152:3306 读操作在master和slave都可以执行 创建可读可写用户： root@(none) 11:07 mysql>create user 'read-write'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) root@(none) 11:10 mysql>grant all on *.* to 'read-write'@'%'; Query OK, 0 rows affected (0.01 sec) root@(none) 11:10 mysql>创建仅可读用户： root@(none) 11:10 mysql>create user 'read-only'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) root@(none) 11:11 mysql>grant select on *.* to 'read-only'@'%'; Query OK, 0 rows affected (0.00 sec) root@(none) 11:12 mysql> 4、启动MySQLrouter [root@keep-02 conf]# mysqlrouter -c /data/mysql/mysql-router-8.0.28/conf/mysqlrouter.conf 七、搭建双vip高可用集群 1、搭建过程 # 同样是做mysqlrouter的两台机器做高可用 keepalived-1 ：192.168.1.148 keepalived-2： 192.168.1.149keep-01 操作： [root@keep-01 /]# yum install keepalived -y [root@keep-01 keepalived]# cd /etc/keepalived/ [root@keep-01 keepalived]# ls keepalived.conf [root@keep-01 keepalived]# vim keepalived.conf [root@keep-01 keepalived]# cat keepalived.conf ! Configuration File for keepalived global_defs { notification_email { [email protected] [email protected] [email protected] } notification_email_from [email protected] smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr #vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { state MASTER interface ens33 virtual_router_id 58 priority 120 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.240 } } vrrp_instance VI_2 { state BACKUP interface ens33 virtual_router_id 59 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.241 } } [root@keep-01 keepalived]#keep-02操作： [root@keep-02 /]# yum install keepalived -y [root@keep-02 /]# cd /etc/keepalived/ [root@keep-02 keepalived]# ls keepalived.conf [root@keep-02 keepalived]# vim keepalived.conf [root@keep-02 keepalived]# cat keepalived.conf ! Configuration File for keepalived global_defs { notification_email { [email protected] [email protected] [email protected] } notification_email_from [email protected] smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr #vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { state BACKUP interface ens33 virtual_router_id 58 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.240 } } vrrp_instance VI_2 { state MASTER interface ens33 virtual_router_id 59 priority 120 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.241 } } [root@keep-02 keepalived]# 2、启动keepalived [root@keep-01 keepalived]# service keepalived restart Redirecting to /bin/systemctl restart keepalived.service [root@keep-01 keepalived]# ps aux | grep keepalived root 4728 0.0 0.1 123012 1408 ? Rs 18:35 0:00 /usr/sbin/keepalived -D root 4729 0.0 0.2 127212 2452 ? S 18:35 0:00 /usr/sbin/keepalived -D root 4730 0.0 0.2 127212 2468 ? S 18:35 0:00 /usr/sbin/keepalived -D root 4740 0.0 0.0 112824 980 pts/0 R+ 18:35 0:00 grep --color=auto keepalived # 第二台keepalived同样的操作 3、双VIP效果 # keep-01效果 [root@keep-01 keepalived]# ip a 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:bf:65:64 brd ff:ff:ff:ff:ff:ff inet 192.168.1.148/24 brd 192.168.1.255 scope global noprefixroute dynamic ens33 valid_lft 4193sec preferred_lft 4193sec inet 192.168.1.240/32 scope global ens33 valid_lft forever preferred_lft forever inet6 fe80::803c:dca6:b254:e8a4/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft forever inet6 fe80::eb4c:4d26:bf10:da92/64 scope link noprefixroute valid_lft forever preferred_lft forever [root@keep-01 keepalived]# # keep-02效果 [root@keep-02 keepalived]# ip a 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:3c:25:77 brd ff:ff:ff:ff:ff:ff inet 192.168.1.149/24 brd 192.168.1.255 scope global noprefixroute dynamic ens33 valid_lft 4194sec preferred_lft 4194sec inet 192.168.1.241/32 scope global ens33 valid_lft forever preferred_lft forever inet6 fe80::eb4c:4d26:bf10:da92/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft forever inet6 fe80::803c:dca6:b254:e8a4/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft forever inet6 fe80::bac:77c2:cf4a:8b22/64 scope link noprefixroute valid_lft forever preferred_lft forever [root@keep-02 keepalived]# # 至此双VIP效果已经实现。 八、搭建监控系统 1、安装Prometheus 第1步：安装prometheus server 源码安装 1.上传下载的源码包到linux服务器 [root@sc-prom ~]# mkdir /prom [root@sc-prom ~]# cd /prom [root@sc-prom prom]# ls prometheus-2.34.0.linux-amd64.tar.gz [root@sc-prom prom]# 2.解压源码包 [root@sc-prom prom]# tar xf prometheus-2.34.0.linux-amd64.tar.gz [root@sc-prom prom]# ls prometheus-2.34.0.linux-amd64 prometheus-2.34.0.linux-amd64.tar.gz [root@sc-prom prom]# 3.修改解压后的压缩包名字 [root@sc-prom prom]# mv prometheus-2.34.0.linux-amd64 prometheus [root@sc-prom prom]# ls prometheus prometheus-2.34.0.linux-amd64.tar.gz [root@sc-prom prom]# # 临时和永久修改PATH变量，添加prometheus的路径 [root@sc-prom prometheus]# PATH=/prom/prometheus:$PATH [root@sc-prom prometheus]# cat /root/.bashrc # .bashrc # User specific aliases and functions alias rm='rm -i' alias cp='cp -i' alias mv='mv -i' # Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi PATH=/prom/prometheus:$PATH #添加 执行prometheus程序 [root@prometheus prometheus]# nohup prometheus --config.file=/prom/prometheus/prometheus.yml & [1] 8431 [root@prometheus prometheus]# nohup: 忽略输入并把输出追加到"nohup.out" [root@prometheus prometheus]# # 查看prometheus的进程 [root@prometheus prometheus]# ps aux|grep prome root 8431 4.5 2.4 782084 46204 pts/0 Sl 11:21 0:00 prometheus --config.file=/prom/prometheus/prometheus.yml root 8439 0.0 0.0 112824 980 pts/0 S+ 11:21 0:00 grep --color=auto prome [root@prometheus prometheus]# # 查看prometheus监听的端口号 [root@prometheus prometheus]# netstat -anplut|grep prome tcp6 0 0 :::9090 :::* LISTEN 8431/prometheus tcp6 0 0 ::1:9090 ::1:51738 ESTABLISHED 8431/prometheus tcp6 0 0 ::1:51738 ::1:9090 ESTABLISHED 8431/prometheus 2、访问测试
压力测试用sysbench测试MySQL集群的整体性能。 十一、总结 本项目是基于mysqrouter、keepalived高可用集群。心得体会如下： 1.一定要规划好整个集群的架构，配置要细心，脚本要提前准备好，边做边修改 2.防火墙和selinux的问题需要多注意 3.对MySQL的集群、读写分离、故障切换和高可用有了深入的理解 4.对自动化批量部署和监控有了更加多的应用和理解 5.keepalived的配置需要更加细心和IP地址的规划有了新的认 |
【本文地址】
今日新闻 |
推荐新闻 |